package com.lifeverse.repository;

import com.lifeverse.entity.NetworkHealthMetrics;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 网络健康指标数据访问层
 */
@Repository
public interface NetworkHealthMetricsRepository extends JpaRepository<NetworkHealthMetrics, Long> {
    
    /**
     * 根据网络类型查找指标
     */
    List<NetworkHealthMetrics> findByNetworkType(NetworkHealthMetrics.NetworkType networkType);
    
    /**
     * 根据网络标识符查找指标
     */
    List<NetworkHealthMetrics> findByNetworkIdentifier(String networkIdentifier);
    
    /**
     * 根据健康状态查找指标
     */
    List<NetworkHealthMetrics> findByHealthStatus(NetworkHealthMetrics.HealthStatus healthStatus);
    
    /**
     * 查找最新的全局网络指标
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.networkType = 'GLOBAL' ORDER BY m.measurementTime DESC")
    List<NetworkHealthMetrics> findLatestGlobalMetrics(Pageable pageable);
    
    /**
     * 根据时间范围查找指标
     */
    List<NetworkHealthMetrics> findByMeasurementTimeBetween(LocalDateTime startTime, LocalDateTime endTime);
    
    /**
     * 根据网络类型和时间范围查找指标
     */
    List<NetworkHealthMetrics> findByNetworkTypeAndMeasurementTimeBetween(
            NetworkHealthMetrics.NetworkType networkType, LocalDateTime startTime, LocalDateTime endTime);
    
    /**
     * 根据健康评分范围查找指标
     */
    List<NetworkHealthMetrics> findByOverallHealthScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找健康的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.overallHealthScore >= 70.0")
    List<NetworkHealthMetrics> findHealthyNetworks();
    
    /**
     * 查找不健康的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.overallHealthScore < 50.0")
    List<NetworkHealthMetrics> findUnhealthyNetworks();
    
    /**
     * 查找需要预警的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.alertLevel >= 3 OR m.overallHealthScore < 50.0")
    List<NetworkHealthMetrics> findNetworksNeedingAlert();
    
    /**
     * 查找有异常的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.anomalyScore < 70.0")
    List<NetworkHealthMetrics> findNetworksWithAnomalies();
    
    /**
     * 根据连通性评分范围查找指标
     */
    List<NetworkHealthMetrics> findByConnectivityScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高连通性网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.connectivityScore >= 80.0")
    List<NetworkHealthMetrics> findHighConnectivityNetworks();
    
    /**
     * 根据稳定性评分范围查找指标
     */
    List<NetworkHealthMetrics> findByStabilityScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找稳定的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.stabilityScore >= 80.0")
    List<NetworkHealthMetrics> findStableNetworks();
    
    /**
     * 根据活跃度评分范围查找指标
     */
    List<NetworkHealthMetrics> findByActivityScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高活跃度网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.activityScore >= 80.0")
    List<NetworkHealthMetrics> findHighActivityNetworks();
    
    /**
     * 根据效率评分范围查找指标
     */
    List<NetworkHealthMetrics> findByEfficiencyScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找高效率网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.efficiencyScore >= 80.0")
    List<NetworkHealthMetrics> findHighEfficiencyNetworks();
    
    /**
     * 根据创新能力评分范围查找指标
     */
    List<NetworkHealthMetrics> findByInnovationScoreBetween(BigDecimal minScore, BigDecimal maxScore);
    
    /**
     * 查找创新能力强的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.innovationScore >= 80.0")
    List<NetworkHealthMetrics> findInnovativeNetworks();
    
    /**
     * 根据节点数量范围查找指标
     */
    List<NetworkHealthMetrics> findByTotalNodesBetween(Integer minNodes, Integer maxNodes);
    
    /**
     * 查找大型网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.totalNodes >= :minSize")
    List<NetworkHealthMetrics> findLargeNetworks(@Param("minSize") Integer minSize);
    
    /**
     * 根据网络密度范围查找指标
     */
    List<NetworkHealthMetrics> findByNetworkDensityBetween(BigDecimal minDensity, BigDecimal maxDensity);
    
    /**
     * 查找高密度网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.networkDensity >= 0.5")
    List<NetworkHealthMetrics> findHighDensityNetworks();
    
    /**
     * 根据响应时间范围查找指标
     */
    List<NetworkHealthMetrics> findByResponseTimeMsBetween(Long minTime, Long maxTime);
    
    /**
     * 查找响应快的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.responseTimeMs <= :maxTime")
    List<NetworkHealthMetrics> findFastResponseNetworks(@Param("maxTime") Long maxTime);
    
    /**
     * 根据错误率范围查找指标
     */
    List<NetworkHealthMetrics> findByErrorRateBetween(BigDecimal minRate, BigDecimal maxRate);
    
    /**
     * 查找低错误率网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.errorRate <= 5.0")
    List<NetworkHealthMetrics> findLowErrorRateNetworks();
    
    /**
     * 根据可用性范围查找指标
     */
    List<NetworkHealthMetrics> findByAvailabilityBetween(BigDecimal minAvailability, BigDecimal maxAvailability);
    
    /**
     * 查找高可用性网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.availability >= 95.0")
    List<NetworkHealthMetrics> findHighAvailabilityNetworks();
    
    /**
     * 查找最近的指标记录
     */
    List<NetworkHealthMetrics> findTop10ByOrderByMeasurementTimeDesc();
    
    /**
     * 查找指定网络类型的最新指标
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.networkType = :networkType ORDER BY m.measurementTime DESC")
    List<NetworkHealthMetrics> findLatestByNetworkType(@Param("networkType") NetworkHealthMetrics.NetworkType networkType, Pageable pageable);
    
    /**
     * 查找指定网络标识符的最新指标
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.networkIdentifier = :identifier ORDER BY m.measurementTime DESC")
    Optional<NetworkHealthMetrics> findLatestByNetworkIdentifier(@Param("identifier") String identifier);
    
    /**
     * 统计健康状态分布
     */
    @Query("SELECT m.healthStatus, COUNT(m) FROM NetworkHealthMetrics m GROUP BY m.healthStatus")
    List<Object[]> countByHealthStatus();
    
    /**
     * 统计网络类型分布
     */
    @Query("SELECT m.networkType, COUNT(m) FROM NetworkHealthMetrics m GROUP BY m.networkType")
    List<Object[]> countByNetworkType();
    
    /**
     * 计算平均健康评分
     */
    @Query("SELECT AVG(m.overallHealthScore) FROM NetworkHealthMetrics m WHERE m.measurementTime >= :since")
    BigDecimal calculateAverageHealthScore(@Param("since") LocalDateTime since);
    
    /**
     * 计算平均响应时间
     */
    @Query("SELECT AVG(m.responseTimeMs) FROM NetworkHealthMetrics m WHERE m.measurementTime >= :since")
    Double calculateAverageResponseTime(@Param("since") LocalDateTime since);
    
    /**
     * 计算平均错误率
     */
    @Query("SELECT AVG(m.errorRate) FROM NetworkHealthMetrics m WHERE m.measurementTime >= :since")
    BigDecimal calculateAverageErrorRate(@Param("since") LocalDateTime since);
    
    /**
     * 计算平均可用性
     */
    @Query("SELECT AVG(m.availability) FROM NetworkHealthMetrics m WHERE m.measurementTime >= :since")
    BigDecimal calculateAverageAvailability(@Param("since") LocalDateTime since);
    
    /**
     * 查找健康评分趋势
     */
    @Query("SELECT m.measurementTime, m.overallHealthScore FROM NetworkHealthMetrics m WHERE m.networkType = :networkType AND m.measurementTime >= :since ORDER BY m.measurementTime")
    List<Object[]> findHealthScoreTrend(@Param("networkType") NetworkHealthMetrics.NetworkType networkType, @Param("since") LocalDateTime since);
    
    /**
     * 查找性能趋势
     */
    @Query("SELECT m.measurementTime, m.responseTimeMs, m.throughputPerSecond, m.errorRate FROM NetworkHealthMetrics m WHERE m.networkType = :networkType AND m.measurementTime >= :since ORDER BY m.measurementTime")
    List<Object[]> findPerformanceTrend(@Param("networkType") NetworkHealthMetrics.NetworkType networkType, @Param("since") LocalDateTime since);
    
    /**
     * 分页查询指标
     */
    Page<NetworkHealthMetrics> findByNetworkType(NetworkHealthMetrics.NetworkType networkType, Pageable pageable);
    
    /**
     * 分页查询指标按健康状态
     */
    Page<NetworkHealthMetrics> findByHealthStatus(NetworkHealthMetrics.HealthStatus healthStatus, Pageable pageable);
    
    /**
     * 查找性能最佳的网络
     */
    @Query("SELECT m FROM NetworkHealthMetrics m WHERE m.measurementTime >= :since ORDER BY (m.efficiencyScore + (100 - m.responseTimeMs/10.0) + m.throughputPerSecond*10 + (100 - m.errorRate) + m.availability) / 5 DESC")
    List<NetworkHealthMetrics> findBestPerformingNetworks(@Param("since") LocalDateTime since, Pageable pageable);
    
    /**
     * 删除过期的指标记录
     */
    @Query("DELETE FROM NetworkHealthMetrics m WHERE m.measurementTime < :cutoffTime")
    void deleteOldMetrics(@Param("cutoffTime") LocalDateTime cutoffTime);
}